package com.wondersgroup.fjzlpt.org.dao.impl.hzgl;


import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.quick.framework.boot.repository.sql.dialect.DialectProxy;
import com.wondersgroup.fjzlpt.org.util.ControllerCommonUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.page.PageQuerySQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.BaseJTDAOImpl;
import com.quick.framework.boot.repository.sql.jdbcTemplate.rs.ResultHandlerConstant;
import com.wondersgroup.fjzlpt.org.dao.inter.hzgl.FpfwDao;

@Repository
public class FpfwDaoImpl extends BaseJTDAOImpl implements FpfwDao {

	@Autowired
	private DialectProxy dialectProxy;

	@Override
	@Autowired
	@Qualifier("TB_HZGL_FPFW")
	public void setTb(TableBean tb) {
		super.setTb(tb);
	}
	
	/**
	 * 查询所有已分配服务
	 * @param map
	 * @return
	 */
	public JSONArray queryAll(Map<String, String> map){
		JSONObject sqlObj = this.getQuerySql(map);
		List<String> sqlParams = (List)sqlObj.get("sqlParams");
		
		SQLBean bean = new SQLBean(sqlObj.getString("sql") + " order by c.JMXXID,a.YQFWSJ", sqlParams.toArray());
		return  super.queryList(bean);
	}
	
	/**
	 * 分页查询已分配服务
	 * @param map
	 * @return
	 */
	@Override
	public JSONObject queryYfprwByPage(Map<String, String> map){
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		ControllerCommonUtil.pageFormat(map);

		JSONObject sqlObj = this.getQuerySql(map);
		JSONArray sqlParams = sqlObj.getJSONArray("sqlParams");
		//排序中将null排前面 ==> oracle:Nulls first  || mysql: is null
		String orderStr = null;
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			orderStr = "order by a.SJFWSJ is null,c.JMXXID,a.YQFWSJ";
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			orderStr = "order by a.SJFWSJ desc Nulls first,c.JMXXID,a.YQFWSJ ";
		}
		PageQuerySQLBean pageBean = super.checkAndCreatePageQuerySQLBean(map,sqlObj.getString("sql"),orderStr,sqlParams.toArray());
		return super.queryPage(pageBean,ResultHandlerConstant.JSONARRAY);
	}
	
	private JSONObject getQuerySql(Map<String, String> map){
		JSONObject result = new JSONObject();
		
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select a.FPFWID,a.QYXXID,a.FWBID,a.FWID,a.YQFWSJ,a.SJFWSJ,a.BZ,a.USERID,a.SFZH,a.FWZT,b.JMXXID,b.YLTDID,c.XM,c.ZJHM,d.FWMC,d.FWNR, \n");
		sqlBuf.append("(select FWBMC from TB_XXWH_FWBJBXX where FWBID=a.FWBID) FWBMC, \n"); //服务包名称
		sqlBuf.append("(select FWBSX from TB_JTQY_FWBGX where FWBID=a.FWBID and QYXXID=a.QYXXID) FWBSX,");
		sqlBuf.append("(select XM from TB_ZD_YLJGRYXX where USERID=a.USERID) FWRY,");//服务人员
		sqlBuf.append("(select XM from TB_ZD_YLJGRYXX where USERID=a.SJFWRYID) SJFWRY,");//实际服务人员
		sqlBuf.append("(case when f.XM is not null then f.XM else c.XM end) FWDX, \n");
		sqlBuf.append("(select TDJS from TB_TDGL_TDRY where TDID=b.YLTDID and USERID=?) TDJS,"); //团队角色：判断是否团队助手
		sqlBuf.append("(select TDMC from TB_TDGL_TDJBXX where TDID = b.YLTDID) QYTD \n"); //签约团队
		sqlBuf.append("from TB_HZGL_FPFW a left join TB_JTQY_QYXX b on a.QYXXID=b.QYXXID \n");
		sqlBuf.append("left join TB_JTQY_JMXX c on b.JMXXID=c.JMXXID \n");
		sqlBuf.append("left join TB_XXWH_FWXX d on a.FWID=d.FWID \n");
		sqlBuf.append("left join TB_JTQY_CYGX f on a.SFZH=f.SFZH and b.JMXXID=f.JMXXID \n");
		sqlBuf.append("left join TB_ZD_YLJGRYXX e on a.USERID=e.USERID where a.FWZT<>2 \n");
		
		List<String> sqlParams = new ArrayList<String>();
		//当前登录人的用户ID，用来关联出当前登陆用户在团队中的角色
		sqlParams.add(map.get("login_userId"));
		//签约居民
		String value = map.get("XM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and c.XM like ? ");
			sqlParams.add("%" + value + "%");
		}
		//服务ID
		value = map.get("FWID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.FWID = ? ");
			sqlParams.add(value);
		}
		//服务人id
		value = map.get("USERID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.USERID = ? ");
			sqlParams.add(value);
		}
		//团队ids
		value = map.get("TDIDS");
		if(!StringUtils.isEmpty(value)){
			String[] tdidArr = value.split(",");
			if(tdidArr.length==1){
				sqlBuf.append("and b.YLTDID=? ");
				sqlParams.add(value);
			}else{
				sqlBuf.append("and b.YLTDID in (");
				for (String tdid : tdidArr) {
					sqlBuf.append("?,");
					sqlParams.add(tdid);
				}
				sqlBuf.deleteCharAt(sqlBuf.length()-1).append(")");
			}
		}
		//服务包id
		value = map.get("FWBID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.FWBID = ? ");
			sqlParams.add(value);
		}
		//预期服务时间
		value = map.get("YQFWSJ");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.YQFWSJ = to_date(?,'yyyy-MM-dd') ");
			sqlParams.add(value);
		}
		//服务人员
		value = map.get("FWRY");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and e.XM like ? ");
			sqlParams.add("%" + value + "%");
		}
		//服务状态
		value = map.get("FWZT");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.FWZT = ? ");
			sqlParams.add(value);
		}
		//签约信息id
		value = map.get("QYXXID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.QYXXID = ? ");
			sqlParams.add(value);
		}
		//服务状态
		value = map.get("FWZT");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.FWZT = ? ");
			sqlParams.add(value);
		}
		
		result.put("sql", sqlBuf.toString());
		result.put("sqlParams", sqlParams);
		
		return result;
	}
	

	/**
	 * 查询已分配的服务次数
	 * @param map
	 * @return
	 */
	@Override
	public JSONArray queryNumberOfYfprw(Map<String, String> map) {
		StringBuffer sqlBuf = new StringBuffer();
		//YFPCS:已分配次数(包含刚分配而未完成的次数)  YWCCS：已完成次数(已经做完的次数)
		sqlBuf.append("select t.QYXXID,t.FWBID,t.FWID,count(*) YFPCS,sum(case when t.FWZT=1 then 1 else 0 end) YWCCS from ( \n");
		sqlBuf.append("select * from TB_HZGL_FPFW a where (a.FWZT=0 or a.FWZT=1) \n");
		
		//签约信息ID
		List<String> sqlParams = new ArrayList<String>();
		String value = map.get("QYXXIDS");
		if(value != null && !"".equals(value)){
			sqlBuf.append("and a.QYXXID in (");
			String[] QYXXIDS = value.split(",");
			for (String QYXXID : QYXXIDS) {
				sqlBuf.append("?,");
				sqlParams.add(QYXXID);
			}
			sqlBuf.deleteCharAt(sqlBuf.length()-1).append(")");
		}
		//服务id
		value = map.get("FWID");
		if(value != null && !"".equals(value)){
			sqlBuf.append("and a.FWID = ? ");
			sqlParams.add(value);
		}
		
		sqlBuf.append(") t group by t.QYXXID,t.FWBID,t.FWID");
		
		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		
		return super.queryList(bean);
	}

	
	/**
	 * 分页查询已经分配的服务项（已完成/待完成）
	 * @param map
	 * @return
	 */
	@Override
	public JSONObject queryPage(Map<String, String> map) {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select a.*,d.FWMC,d.FWNR,c.TDMC,e.XM, \n");
		sqlBuf.append("(select XM from TB_ZD_YLJGRYXX where USERID=a.USERID) FWRY,"); //服务人员
		sqlBuf.append("(select FWBMC from TB_XXWH_FWBJBXX where FWBID=a.FWBID) FWBMC, \n");//服务包名称
		sqlBuf.append("(case when f.XM is not null then f.XM else e.XM end) FWDX  \n");//服务对象
		sqlBuf.append("from TB_HZGL_FPFW a left join TB_JTQY_QYXX b on a.QYXXID=b.QYXXID \n");
		sqlBuf.append("left join TB_TDGL_TDJBXX c on b.YLTDID=c.TDID \n");
		sqlBuf.append("left join TB_XXWH_FWXX d on a.FWID=d.FWID \n");
		sqlBuf.append("left join TB_JTQY_CYGX f on a.SFZH=f.SFZH and b.JMXXID=f.JMXXID \n");
		sqlBuf.append("left join TB_JTQY_JMXX e on b.JMXXID=e.JMXXID where 1=1 ");
		
		List<String> sqlParams = new ArrayList<String>();
		//签约信息id
		String value = map.get("QYXXID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and b.QYXXID = ? ");
			sqlParams.add(value);
		}
		//服务人id
		value = map.get("USERID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.USERID = ? ");
			sqlParams.add(value);
		}
		//预期服务时间
		value = map.get("YQFWSJ");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.YQFWSJ = ? ");
			sqlParams.add(value);
		}
		//所属团队id
		value = map.get("TDIDS");
		if(!StringUtils.isEmpty(value)){
			String[] tdidArr = value.split(",");
			sqlBuf.append("and C.TDID in (");
			for (String TDID : tdidArr) {
				sqlBuf.append("?,");
				sqlParams.add(TDID);
			}
			sqlBuf.deleteCharAt(sqlBuf.length()-1).append(") ");
		}
		//姓名
		value = map.get("XM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and e.XM like ? ");
			sqlParams.add("%"+value+"%");
		}
		//服务状态
		value = map.get("FWZT");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.FWZT = ? ");
			sqlParams.add(value);
		}
		
		
//		int pageSize = Integer.parseInt(map.get("rows"));
//		int pageNumber = Integer.parseInt(map.get("page"));
//		PageQuerySQLBean pageBean = new PageQuerySQLBean(sqlBuf.toString(), pageSize, pageNumber, "order by a.SJFWSJ desc,a.YQFWSJ desc", sqlParams.toArray());
		ControllerCommonUtil.pageFormat(map);
		PageQuerySQLBean pageBean = super.checkAndCreatePageQuerySQLBean(map, sqlBuf.toString(), "order by a.SJFWSJ desc,a.YQFWSJ desc", sqlParams.toArray());
		
		return super.queryPage(pageBean, ResultHandlerConstant.JSONARRAY);
	}

	/**
	 * 查询某服务项已经完成的次数和总次数
	 * @param map
	 * @return
	 */
	@Override
	public JSONObject queryNumberOfYwcrw(Map<String, String> map) {
		StringBuffer sqlBuf = new StringBuffer("select sum(case when FWZT=1 then 1 else 0 end) YFWCS, \n");
		//服务频次--服务包专有
		sqlBuf.append("(select FWPC from TB_XXWH_FWB_FWXX where FWBID=a.FWBID and FWID=a.FWID) FWPC, \n");
		//服务次数--医保服务项专有
		sqlBuf.append("(select FWCS from TB_JTQY_YBFWXGX where QYXXID=a.QYXXID and FWID=a.FWID) FWCS \n");
		sqlBuf.append("from TB_HZGL_FPFW a where a.FWID=? and a.FWBID=? and a.QYXXID = ? \n");
		sqlBuf.append("group by a.QYXXID,a.FWBID,a.FWID");
		
		SQLBean bean = new SQLBean(sqlBuf.toString(),map.get("FWID"), map.get("FWBID"), map.get("QYXXID"));
		return super.queryOne(bean);
	}
	
	
	/**
	 * 作废服务（用于同意转介）
	 * @param QYXXID
	 * @return
	 */
	public void freezeYfprw(String QYXXID){
		String sql = "update TB_HZGL_FPFW set FWZT = 2 where FWZT = 0 and QYXXID = ?";
		SQLBean bean = new SQLBean(sql, QYXXID);
		super.update(bean);
	}
	
	/**
	 * 分页查询签约服务列表到健康城市系统
	 * @param map
	 * @return
	 */
	public JSONObject queryQyfwByPageToJkcs(Map<String, String> map){
		ControllerCommonUtil.pageFormat(map);
		boolean flag = map.containsKey("YSID");
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select c.FWBID,c.FWPC,d.FWID,d.FWMC,d.FWNR,e.QYXXID,e.SCFWSJ,e.FWYS,e.FWYSID,e.FPFWID,f.FWBMC ");
		if(flag){
			sqlBuf.append(",g.FPFWID FPFWIDYS, g.FWYS FWYSXMYS, g.FWYSID FWYSIDYS  ");
		}
		sqlBuf.append("from TB_JTQY_QYXX a left join TB_JTQY_FWBGX b on a.QYXXID=b.QYXXID \n");
		sqlBuf.append("left join TB_XXWH_FWB_FWXX c on b.FWBID=c.FWBID \n");
		sqlBuf.append("left join TB_XXWH_FWXX d on c.FWID=d.FWID \n");
		//e表查询：e表记录是根据QYXXID和FWID分组后获取的最近已经完成的服务
		sqlBuf.append("left join (select aa.QYXXID,aa.FWID,aa.FPFWID,aa.SJFWSJ SCFWSJ,(select XM from TB_ZD_YLJGRYXX where USERID=aa.USERID) FWYS,aa.USERID FWYSID from TB_HZGL_FPFW aa where aa.FWZT=1 group by AA.QYXXID, AA.FWID,AA.FPFWID, AA.SJFWSJ,AA.USERID order by aa.SJFWSJ) e \n");
		sqlBuf.append("on a.QYXXID=e.QYXXID and d.FWID=e.FWID \n");
		if(flag){
			sqlBuf.append("left join (select aa.QYXXID,aa.FWID,aa.FPFWID,aa.SJFWSJ SCFWSJ,(select XM from TB_ZD_YLJGRYXX where USERID=aa.USERID) FWYS,aa.USERID FWYSID from TB_HZGL_FPFW aa where aa.FWZT=0 group by AA.QYXXID, AA.FWID,AA.FPFWID, AA.SJFWSJ,AA.USERID order by aa.SJFWSJ) g \n");
			sqlBuf.append("on a.QYXXID=g.QYXXID and d.FWID=g.FWID \n");
		}
		sqlBuf.append("left join TB_XXWH_FWBJBXX f on f.FWBID=b.FWBID where a.QYXXID = ? ");
		
//		PageQuerySQLBean pageBean = super.checkAndCreatePageQuerySQLBean(map, sqlBuf.toString(), "order by c.FWBID,d.FWID", map.get("QYXXID"));
//		return super.queryPage(pageBean,ResultHandlerConstant.JSONARRAY);
		SQLBean bean;
//		if(flag)
//			bean = new SQLBean(sqlBuf.toString(),map.get("YSID"),map.get("QYXXID"));
//		else
			bean = new SQLBean(sqlBuf.toString(),map.get("QYXXID"));
		JSONArray fwjlArr = super.queryList(bean);
		
		JSONObject result = new JSONObject();
		result.put("row", fwjlArr);
		return result;
	}
	
	/**
	 * 查询已完成服务列表到健康城市系统
	 * @param map
	 * @return
	 */
	public JSONObject queryYwcfwByPageToJkcs(Map<String, String> map){
		ControllerCommonUtil.pageFormat(map);
		JSONObject sqlObj = this.getQuerySql(map);
		List<String> sqlParams = (List)sqlObj.get("sqlParams");
		PageQuerySQLBean pageBean = super.checkAndCreatePageQuerySQLBean(map, sqlObj.getString("sql"), "order by a.SJFWSJ", sqlParams.toArray());
		return super.queryPage(pageBean,ResultHandlerConstant.JSONARRAY);
	}

	/**
	 * 查询"查看服务"的标签页统计数目
	 * @param map
	 * @return
	 */
	public JSONObject queryCountInCkfw(Map<String, String> map){
		String QYXXID = map.get("QYXXID");

		StringBuffer sqlBuf = new StringBuffer();
		//YWCFWCS:已完成服务次数；  DWCFWCS:待完成服务次数
		sqlBuf.append("select sum(case when fwzt=1 then 1 else 0 end) YWCFWCS, sum(case when fwzt=0 then 1 else 0 end) DWCFWCS  from ");
		sqlBuf.append(this.tb.getTableName()).append(" where QYXXID = ? \n");
		SQLBean bean = new SQLBean(sqlBuf.toString(),QYXXID);

		JSONObject result = super.query(bean,ResultHandlerConstant.JSONOBJECT);

		//查询"待分配服务"标签页总数
		StringBuffer sqlBuf2 = new StringBuffer();
		sqlBuf2.append("select count(t.FWID) DFPFWCS \n");
		sqlBuf2.append("from (");
		sqlBuf2.append("select c.FWID,a.QYXXID from TB_JTQY_QYXX a left join TB_JTQY_FWBGX b on a.QYXXID=b.QYXXID \n");
		sqlBuf2.append("left join TB_XXWH_FWB_FWXX c on b.FWBID=c.FWBID \n");

		sqlBuf2.append("union all \n");

		//医保服务项
		sqlBuf2.append("select FWID,QYXXID from TB_JTQY_YBFWXGX) t where t.QYXXID = ? ");
		SQLBean bean2 = new SQLBean(sqlBuf2.toString(),QYXXID);

		JSONObject dfwfwcsObj = super.query(bean2,ResultHandlerConstant.JSONOBJECT);
		result.put("DFPFWCS",dfwfwcsObj.getString("DFPFWCS"));
		return result;
	}
	
}
